﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Windows.Forms;


namespace DAO
{
    public class DataProvider
    {
        private static string constr;
        private static OleDbConnection conn;
        private static OleDbDataAdapter da = null;
        private static  OleDbCommand cmd;
        private static DataSet ds;


        public DataProvider()
        {
            constr = "Provider = Microsoft.Jet.OleDb.4.0;Data Source=..\\QuanLyKhachSan.mdb";
        }

        public  void Connect()
        {
            try
            {
                conn = new OleDbConnection(constr);
                conn.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
            }
        }

        public void disconnect()
        {
            try
            {
                conn.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
            }
        }

        public  DataTable ExecuteQuery(String strQuery)
        {
            DataTable resTable = new DataTable();
            try
            {
                Connect();
                da = new OleDbDataAdapter(strQuery, conn);
                da.Fill(resTable);
               
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc hien SQL :" + ex.Message);
            }
            finally
            {
                disconnect();
            }
            return resTable;

        }
        //Dung cho cac thao tac insert, delete, update
        public static void ExecuteNonQuery(string sql)
        {
            try
            {
                conn.Open();
                cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi lenh SQL: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static void ThucHienCmd(string select)
        {
            cmd = new OleDbCommand(select, conn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (OleDbException se)
            {
                MessageBox.Show("Lỗi cơ sở dữ liệu! Hãy nhấn F1!");
                MessageBox.Show("" + se.Message);
            }
        }

        public static DataSet GrdSource(string select)
        {
            da = new OleDbDataAdapter(select, conn);
            ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        public static OleDbDataReader ThucHienReader(string select)
        {
            cmd = new OleDbCommand(select, conn);
            try
            {
                return cmd.ExecuteReader();
            }
            catch (OleDbException)
            {
                return null;
            }
        }

        //Lay 1 gia tri du lieu ra
        public object executeScalar(string sql)
        {
            Connect();
            cmd = new OleDbCommand(sql, conn);
            object o = cmd.ExecuteScalar();
            disconnect();
            return o;
        }

    }
}
